1. Coincidente o quasi con gli esercizi proposti il 12/10/2022
https://forms.office.com/Pages/ResponsePage.aspx?id=aN-0_2T0jEWlRgD7OvZvam9yL2t3wgdCu9Xa8dCaqodUODRMUldJM1o3UExVTlVaUDFMNlZUNDhWSy4u

2. Coincidente o quasi con la domanda 1 del 16/11/2015
https://www.inf.uniroma3.it/~atzeni/didattica/BDN/compitiPDF/parziale20151116.pdf

Per le interrogazioni SQL

CREATE TABLE Compositori ( codice integer NOT NULL PRIMARY KEY,
                        cognome text ,
                        nome text );
CREATE TABLE Pezzi (     codice  integer NOT NULL PRIMARY KEY,
                        titolo text,
                        autore integer NOT NULL REFERENCES Compositori,
                        durata integer);
CREATE TABLE Concerti ( codice integer NOT NULL PRIMARY KEY,
                        titolo text ,
                        descrizione text );
CREATE TABLE Programmazione ( pezzo integer NOT NULL REFERENCES Pezzi,
                        concerto integer NOT NULL REFERENCES Concerti, 
                        posizione integer,
                        PRIMARY KEY(pezzo, concerto) );

INSERT INTO Compositori VALUES (11,'Rossi', 'Mario');
INSERT INTO Compositori VALUES (12,'Bruni', 'Luca');
INSERT INTO Compositori VALUES (13,'Neri', 'Gino');

INSERT INTO Pezzi VALUES (1,'Sonata X', 11, 5);
INSERT INTO Pezzi VALUES (2,'Ballata Y', 11, 8);
INSERT INTO Pezzi VALUES (3,'Ballata X', 12, 7);
INSERT INTO Pezzi VALUES (4,'Sinfonia X', 13, 11);
INSERT INTO Pezzi VALUES (5,'Sinfonia X', 12, 21);
INSERT INTO Pezzi VALUES (6,'Sinfonia Z', 12, 21);

INSERT INTO Concerti VALUES (21,'Bla', 'BlaBlaBla');
INSERT INTO Concerti VALUES (22,'Tic', 'TicTicTic');
INSERT INTO Concerti VALUES (23,'Boh', 'BohBohBoh');
INSERT INTO Concerti VALUES (32,'Gold', 'GoldGold');

INSERT INTO Programmazione VALUES (1,21,1);
INSERT INTO Programmazione VALUES (2,21,2);
INSERT INTO Programmazione VALUES (3,21,3);
INSERT INTO Programmazione VALUES (2,22,1);
INSERT INTO Programmazione VALUES (3,22,2);
INSERT INTO Programmazione VALUES (1,23,1);
INSERT INTO Programmazione VALUES (2,23,2);



3.
Scrivere l'interrogazione SQL che trova i pezzi di durata maggiore di 5 il cui autore è un compositore di cognome 'Rossi'. Mostrare codice del compositore e codice, titolo e durata del pezzo. Ordinare per codice del compositore e codice del pezzo

SELECT pezzi.autore AS codiceCompositore,pezzi.codice AS codicePezzo,Pezzi.titolo,pezzi.durata
FROM Pezzi Join Compositori ON Pezzi.autore=Compositori.codice
Group BY(Compositori.codice)
Having MAX(durata) AND Compositori.cognome = 'Rossi'
Order by(Compositori.codice)

4a.
Scrivere l'interrogazione SQL che trova 
codice e titolo dei pezzi che non sono presenti in nessun concerto
Ordinare per codice

select codice, titolo
FROM Pezzi
EXCEPT
SELECT codice, titolo
FROM Pezzi join Programmazione ON codice=pezzo
ORDER BY codice


4b. 
Scrivere l'interrogazione SQL che trova codice e titolo dei concerti per i quali nella base di dati non c'è nessun pezzo in programmazione
Ordinare per codice

select codice, titolo
FROM Concerti
EXCEPT
SELECT codice, titolo
FROM Concerti join Programmazione ON codice=concerto
ORDER BY codice


4c
Scrivere l'interrogazione SQL che trova codice, cognome e nome dei compositori per i quali non c'è nessun pezzo in programmazione.
Ordinare per codice

SELECT codice, cognome, nome
FROM Compositori
EXCEPT
SELECT C.codice, cognome, nome
FROM Compositori C JOIN Pezzi Z on C.codice=autore
                   join Programmazione ON Z.codice=pezzo
ORDER BY codice

5a 
Scrivere l'interrogazione SQL che trova, per ogni compositore, il numero di pezzi diversi suonati in almeno un concerto. Trascurare i compositori per i quali non è suonato alcun pezzo
Mostrare codice, cognome, nome e numero di pezzi. 

SELECT C.codice, C.cognome, C.nome, count(distinct pezzo) as numeroPezzi
from Compositori C  JOIN Pezzi Z ON C.codice=Z.autore
                 JOIN Programmazione P ON Z.codice=pezzo
GROUP by  C.codice, C.cognome, C.nome 
ORDER BY  C.codice

5b 
Scrivere l'interrogazione SQL che trova, per ogni concerto che abbia almeno un pezzo, la durata totale (somma delle durate dei pezzi). 
Mostrare codice e titolo del concerto e durata totale.
Ordinare per codice.

SELECT C.codice, C.titolo, sum(durata) as durataTotale
from Concerti C  JOIN Programmazione ON C.codice=concerto
                 JOIN Pezzi P ON pezzo=P.codice
GROUP by  C.codice, C.titolo;


5c 
Scrivere l'interrogazione SQL che trova, per ogni concerto che abbia almeno un pezzo, il numero dei pezzi.
Mostrare codice e titolo del concerto e durata totale.
Ordinare per codice.

SELECT C.codice, C.titolo, count(pezzo) as numeroPezzi
from Concerti C  JOIN Programmazione ON C.codice=concerto
                 JOIN Pezzi P ON pezzo=P.codice
GROUP by  C.codice, C.titolo 
ORDER BY  C.codice

6a 

Scrivere l'interrogazione SQL che trova il compositore (o i compositori) con il massimo numero di pezzi diversi suonati in almeno un concerto (come calcolati in risposta alla domanda precedente). Mostrare codice, nome, cognome e numero di 
pezzi e ordinare per codice.

create view numeroPezziPerCompositore as 
SELECT C.codice, C.cognome, C.nome, count(distinct pezzo) as numeroPezzi
from Compositori C  JOIN Pezzi Z ON C.codice=Z.autore
                 JOIN Programmazione P ON Z.codice=pezzo
GROUP by  C.codice, C.cognome, C.nome 
ORDER BY  C.codice;
SELECT *
from numeroPezziPerCompositore 
where numeroPezzi = (SELECT max(numeroPezzi) 
                    from numeroPezziPerCompositore)


6b
Scrivere l'interrogazione SQL che trova il concerto (o i concerti) con la massima durata totale (come calcolata in risposta alla domanda precedente). Mostrare codice, titolo e durata e ordinare per codice.

create view concertoConDurata as
SELECT C.codice, C.titolo, sum(durata) as durataTotale
from Concerti C  JOIN Programmazione ON C.codice=concerto
                 JOIN Pezzi P ON pezzo=P.codice
GROUP by  C.codice, C.titolo;
select *
FROM concertoConDurata
WHERE durataTotale = (SELECT max(durataTotale) FROM concertoConDurata)


3c
Scrivere l'interrogazione SQL che trova il concerto (o i concerti) con il massimo numero di pezzi (come calcolato in risposta alla domanda precedente). Mostrare codice, titolo e numero di pezzi e ordinare per codice.

create view concertoConNumeroPezzi as
SELECT C.codice, C.titolo, count(pezzo) as numeroPezzi
from Concerti C  JOIN Programmazione ON C.codice=concerto
                 JOIN Pezzi P ON pezzo=P.codice
GROUP by  C.codice, C.titolo 
ORDER BY  C.codice;
select *
FROM concertoConNumeroPezzi
WHERE numeroPezzi = (SELECT max(numeroPezzi) FROM concertoConNumeroPezzi)